ALTER TABLE CHI_TIEU
ADD
MA_PHIEU_THU INT FOREIGN KEY REFERENCES THU_TIEN(ID);


INSERT INTO MASTER
(
	NAME, 
	VAL,
	ACTIVE
)VALUES
(
	'ELE_PRICE', 
	'3000', 
	'A'
)



INSERT INTO MASTER
(
	NAME, 
	VAL,
	ACTIVE
)VALUES
(
	'WAT_PRICE', 
	'3000', 
	'A'
)


ALTER PROC [dbo].[SP_GET_INFOR_BY_THUE_PHONG]
@ID INT
AS
BEGIN
	SELECT 
		THUE_PHONG.MA_PHONG,
		PHONG.SO_PHONG,
		THUE_PHONG.ID, 
		KHACH_THUE.HO_VA_TEN,
		DBO.F_GET_CHI_SO_NUOC(THUE_PHONG.ID) CHI_SO_NUOC, 
		DBO.F_GET_CHI_SO_DIEN(THUE_PHONG.ID) CHI_SO_DIEN,
		DBO.F_GET_NGAY_KET_THUC(THUE_PHONG.ID) NGAY_KET_THUC, 
		ISNULL(THUE_PHONG.DON_GIA_PHONG,0) DON_GIA_PHONG, 
		ISNULL(THUE_PHONG.TIEN_COC,0) TIEN_COC
	FROM
	THUE_PHONG,
	KHACH_THUE, 
	PHONG
	WHERE
	THUE_PHONG.ACTIVE = 'A'
	AND KHACH_THUE.ACTIVE = 'A'
	AND KHACH_THUE.LEVEL = 1
	AND THUE_PHONG.ID = @ID
	AND THUE_PHONG.ID = KHACH_THUE.SO_THUE_PHONG
	AND THUE_PHONG.MA_PHONG = PHONG.ID	
END



ALTER PROC [dbo].[SP_INSERT_THU_TIEN]
@SO_THUE_PHONG INT, 
@NGAY_THU DATE, 
@NGUOI_THU INT, 
@NGAY_KT_KY_TRUOC DATE, 
@NGAY_KET_THUC_KY_NAY DATE, 
@CHI_SO_NUOC_TRUOC FLOAT, 
@CHI_SO_NUOC_SAU FLOAT,
@CHI_SO_DIEN_TRUOC FLOAT, 
@CHI_SO_DIEN_SAU FLOAT,
@DON_GIA_DIEN FLOAT, 
@DON_GIA_NUOC FLOAT, 
@TIEN_PHONG FLOAT,
@TIEN_GUI_XE FLOAT,
@TIEN_PHU_THU FLOAT,
@SO_THANG INT
AS
BEGIN
	INSERT INTO THU_TIEN
	(
		SO_THUE_PHONG,
		NGAY_THU,
		NGUOI_THU,
		NGAY_KT_KY_TRUOC,
		NGAY_KET_THUC_KY_NAY,
		CHI_SO_NUOC_TRUOC,
		CHI_SO_NUOC_SAU,
		CHI_SO_DIEN_TRUOC,
		CHI_SO_DIEN_SAU,
		DON_GIA_DIEN,
		DON_GIA_NUOC,
		TIEN_PHONG,
		TIEN_GUI_XE,
		TIEN_PHU_THU,
		SO_THANG,
		CREATE_BY,
		UPDATE_BY,
		CREATE_AT,
		UPDATE_AT,
		ACTIVE
	)
	VALUES
	(
		@SO_THUE_PHONG , 
		@NGAY_THU , 
		@NGUOI_THU , 
		@NGAY_KT_KY_TRUOC , 
		@NGAY_KET_THUC_KY_NAY , 
		@CHI_SO_NUOC_TRUOC , 
		@CHI_SO_NUOC_SAU ,
		@CHI_SO_DIEN_TRUOC , 
		@CHI_SO_DIEN_SAU ,
		@DON_GIA_DIEN , 
		@DON_GIA_NUOC , 
		@TIEN_PHONG ,
		@TIEN_GUI_XE ,
		@TIEN_PHU_THU ,
		@SO_THANG,
		@NGUOI_THU,
		@NGUOI_THU,
		GETDATE(), 
		GETDATE(), 
		'A' 
	)	
	
	DELETE THU_TIEN 
	WHERE ACTIVE = 'P' AND SO_THUE_PHONG = @SO_THUE_PHONG;
	
	UPDATE CHI_TIEU
	SET 
	ACTIVE = 'C', -- COMPLETE,
	MA_PHIEU_THU =  (SELECT SCOPE_IDENTITY())
	WHERE ACTIVE = 'A' AND SO_THUE_PHONG = @SO_THUE_PHONG;
	
END


CREATE PROC SP_TRA_PHONG
@SO_THUE_PHONG INT, 
@NGAY_TRA DATE,
@CHI_SO_NUOC_TRUOC FLOAT, 
@CHI_SO_NUOC_SAU FLOAT, 
@DON_GIA_NUOC FLOAT, 
@CHI_SO_DIEN_TRUOC FLOAT, 
@CHI_SO_DIEN_SAU FLOAT,
@DON_GIA_DIEN FLOAT, 
@CHI_PHI_KHAC FLOAT ,
@USR_ID INT
AS
BEGIN
	UPDATE THUE_PHONG
	SET 
	NGAY_TRA = @NGAY_TRA, 
	UPDATE_AT = GETDATE(), 
	UPDATE_BY = @USR_ID
	WHERE ID = @SO_THUE_PHONG;
	
	INSERT INTO THU_TIEN
	(
		SO_THUE_PHONG, 
		NGAY_THU, 
		CHI_SO_NUOC_TRUOC, 
		CHI_SO_NUOC_SAU, 
		DON_GIA_NUOC, 
		CHI_SO_DIEN_TRUOC, 
		CHI_SO_DIEN_SAU, 
		DON_GIA_DIEN, 
		TIEN_PHU_THU, 
		CREATE_BY, 
		CREATE_AT, 
		ACTIVE
	)
	VALUES
	(
		@SO_THUE_PHONG, 
		@NGAY_TRA, 
		@CHI_SO_NUOC_TRUOC,
		@CHI_SO_NUOC_SAU,
		@DON_GIA_NUOC,
		@CHI_SO_DIEN_TRUOC,
		@CHI_SO_DIEN_SAU,
		@DON_GIA_DIEN,
		@CHI_PHI_KHAC, 
		@USR_ID, 
		GETDATE(), 
		'A'
	)
	
	UPDATE CHI_TIEU
	SET 
	ACTIVE = 'C', -- COMPLETE,
	MA_PHIEU_THU =  (SELECT SCOPE_IDENTITY())
	WHERE ACTIVE = 'A' AND SO_THUE_PHONG = @SO_THUE_PHONG;
END


CREATE FUNCTION F_GET_ROOM_STATUS
(
	@ID INT
)
RETURNS INT
AS
BEGIN
	IF (SELECT COUNT(MA_PHONG)FROM THUE_PHONG WHERE ACTIVE = 'A' AND NGAY_TRA IS NULL AND MA_PHONG = @ID) > 0
	BEGIN
		RETURN 2 -- ĐANG CHO THUÊ
	END
	ELSE
	BEGIN
		RETURN 1 -- CÒN TRỐNG
	END
	RETURN 1
END

ALTER PROC [dbo].[SP_GET_PHONG]
@SO_PHONG INT,
@TINH_TRANG INT
AS
BEGIN
	SELECT 
		ID,
		SO_PHONG,
		ISNULL(DON_GIA,0) DON_GIA, 
		ISNULL(DIEN_TICH,0) DIEN_TICH, 
		GHI_CHU, 
		DBO.F_GET_ROOM_STATUS(ID) TINH_TRANG
	FROM 
	PHONG
	WHERE
	(@SO_PHONG = 0 OR PHONG.SO_PHONG = @SO_PHONG)
	AND (@TINH_TRANG = 0 OR 
		(@TINH_TRANG = 1 AND PHONG.ID NOT IN (SELECT MA_PHONG FROM THUE_PHONG WHERE ACTIVE = 'A' AND NGAY_TRA IS NULL)) OR
		(@TINH_TRANG = 2 AND PHONG.ID IN (SELECT MA_PHONG FROM THUE_PHONG WHERE ACTIVE = 'A' AND NGAY_TRA IS NULL)))
	AND PHONG.ACTIVE = 'A'
END



CREATE PROC SP_GET_PHONG_FOR_EDIT
@SO_PHONG INT
AS
BEGIN
	 SELECT 
		ID,
		SO_PHONG,
		ISNULL(DON_GIA,0) DON_GIA, 
		ISNULL(DIEN_TICH,0) DIEN_TICH, 
		GHI_CHU, 
		DBO.F_GET_ROOM_STATUS(ID) TINH_TRANG
	FROM 
	PHONG
	WHERE
	(PHONG.ID = @SO_PHONG)
	OR( 
			PHONG.ID NOT IN (SELECT MA_PHONG FROM THUE_PHONG WHERE ACTIVE = 'A' AND NGAY_TRA IS NULL)
	  )
	AND PHONG.ACTIVE = 'A'
END


CREATE FUNCTION F_COUNT_COLLECT_HIST
(@ID INT)
RETURNS INT
AS
BEGIN 
	RETURN (SELECT COUNT(ID) FROM THU_TIEN WHERE ACTIVE = 'A' AND SO_THUE_PHONG = @ID)
END


CREATE PROC SP_GET_INFOR_OF_THUE_PHONG_4_EDIT
@ID INT
AS
BEGIN
	SELECT  
		THUE_PHONG.ID,
		NGAY_THUE, 
		ISNULL(TIEN_COC,0) TIEN_COC,
		ISNULL(DON_GIA_PHONG,0) DON_GIA_PHONG, 
		THU_TIEN.ID THU_TIEN_ID, 
		ISNULL(THU_TIEN.CHI_SO_NUOC_SAU,0) CHI_SO_NUOC, 
		ISNULL(THU_TIEN.CHI_SO_DIEN_SAU,0) CHI_SO_DIEN, 
		THU_TIEN.NGAY_KET_THUC_KY_NAY NGAY_VAO_O, 
		DBO.F_COUNT_COLLECT_HIST(@ID) AS NCOUNT
	FROM 
		THUE_PHONG, 
		(SELECT TOP 1 ID, SO_THUE_PHONG, NGAY_KET_THUC_KY_NAY, CHI_SO_NUOC_SAU,  CHI_SO_DIEN_SAU FROM THU_TIEN WHERE ACTIVE = 'A' AND SO_THUE_PHONG = @ID ORDER BY ID ) THU_TIEN
	WHERE 
	THUE_PHONG.ACTIVE = 'A'
	AND THUE_PHONG.ID = @ID
	AND THUE_PHONG.ID = THU_TIEN.SO_THUE_PHONG
END


ALTER PROC [dbo].[SP_INSERT_THU_TIEN]
@SO_THUE_PHONG INT, 
@NGAY_THU DATE, 
@NGUOI_THU INT, 
@NGAY_KT_KY_TRUOC DATE, 
@NGAY_KET_THUC_KY_NAY DATE, 
@CHI_SO_NUOC_TRUOC FLOAT, 
@CHI_SO_NUOC_SAU FLOAT,
@CHI_SO_DIEN_TRUOC FLOAT, 
@CHI_SO_DIEN_SAU FLOAT,
@DON_GIA_DIEN FLOAT, 
@DON_GIA_NUOC FLOAT, 
@TIEN_PHONG FLOAT,
@TIEN_GUI_XE FLOAT,
@TIEN_PHU_THU FLOAT,
@SO_THANG INT
AS
BEGIN
	INSERT INTO THU_TIEN
	(
		SO_THUE_PHONG,
		NGAY_THU,
		NGUOI_THU,
		NGAY_KT_KY_TRUOC,
		NGAY_KET_THUC_KY_NAY,
		CHI_SO_NUOC_TRUOC,
		CHI_SO_NUOC_SAU,
		CHI_SO_DIEN_TRUOC,
		CHI_SO_DIEN_SAU,
		DON_GIA_DIEN,
		DON_GIA_NUOC,
		TIEN_PHONG,
		TIEN_GUI_XE,
		TIEN_PHU_THU,
		SO_THANG,
		CREATE_BY,
		UPDATE_BY,
		CREATE_AT,
		UPDATE_AT,
		ACTIVE
	)
	VALUES
	(
		@SO_THUE_PHONG , 
		@NGAY_THU , 
		@NGUOI_THU , 
		CASE @SO_THANG 
		WHEN 0 THEN
			NULL
		ELSE
			@NGAY_KT_KY_TRUOC 
		END,
		CASE @SO_THANG 
		WHEN 0 THEN
			NULL
		ELSE
			@NGAY_KET_THUC_KY_NAY 
		END, 
		@CHI_SO_NUOC_TRUOC , 
		@CHI_SO_NUOC_SAU ,
		@CHI_SO_DIEN_TRUOC , 
		@CHI_SO_DIEN_SAU ,
		@DON_GIA_DIEN , 
		@DON_GIA_NUOC , 
		@TIEN_PHONG ,
		@TIEN_GUI_XE ,
		@TIEN_PHU_THU ,
		@SO_THANG,
		@NGUOI_THU,
		@NGUOI_THU,
		GETDATE(), 
		GETDATE(), 
		'A' 
	)	
	
	DELETE THU_TIEN 
	WHERE ACTIVE = 'P' AND SO_THUE_PHONG = @SO_THUE_PHONG;
	
	UPDATE CHI_TIEU
	SET 
	ACTIVE = 'C', -- COMPLETE,
	MA_PHIEU_THU =  (SELECT SCOPE_IDENTITY())
	WHERE ACTIVE = 'A' AND SO_THUE_PHONG = @SO_THUE_PHONG;
	
END